﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OPTFleet.OPTFleet;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace OPTFleet.Data
{
    class GestaoVeiculos
    {
        private static String tabelaVeiculos = "veiculos";
        private static String[] chavesVeiculos = { "idveiculo" };
        private static String[] camposVeiculos = { "matricula", "idtipo", "ano", "largura", "comprimento", "deposito", "consumomedio" };

        private Conexao sqlConnection;

        public Conexao SqlConnection
        {
            set { sqlConnection = value; }
        }

        public GestaoVeiculos()
        {
            this.sqlConnection = new Conexao();
            this.sqlConnection.connect();
        }

        private SortedDictionary<Int32, String> MapTiposVeiculo(DbDataReader rs)
        {
            SqlDataReader sdr;
            SortedDictionary<Int32, String> sd = new SortedDictionary<Int32, String>();

            sdr = (SqlDataReader)rs;

            while (rs.Read())
            {
                sd.Add((int)sdr["idtipo"], (string)sdr["tipoveiculo"]);
            }
            return sd;
        }

        public SortedDictionary<Int32,String> ConsultarTiposVeiculos()
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            SqlDataReader rs;
            SortedDictionary<Int32,String> sd;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "SELECT idtipo,tipoveiculo FROM tiposveiculo ORDER BY idtipo ASC";
            sqlCmd = new SqlCommand(stmtSql,sqlCon);
            rs = sqlCmd.ExecuteReader();

            sd = MapTiposVeiculo(rs);
            rs.Close();
            return sd;
        }

        public Veiculo ConsultarVeiculo(int idVeiculo)
        {
            Veiculo veiculo;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String[] parameters = new String[15];
            String stmtSql;
            SqlDataReader rs;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            parameters[0] = tabelaVeiculos + "." + chavesVeiculos[0];
            parameters[1] = tabelaVeiculos + "." + camposVeiculos[0];
            parameters[2] = tabelaVeiculos + "." + camposVeiculos[1];
            parameters[3] = tabelaVeiculos + "." + camposVeiculos[2];
            parameters[4] = tabelaVeiculos + "." + camposVeiculos[3];
            parameters[5] = tabelaVeiculos + "." + camposVeiculos[4];
            parameters[6] = tabelaVeiculos + "." + camposVeiculos[5];
            parameters[7] = tabelaVeiculos + "." + camposVeiculos[6];
            parameters[8] = tabelaVeiculos;
            parameters[9] = tabelaVeiculos + "." + chavesVeiculos[0];

            /*
            SELECT idveiculo,matricula,idtipo,ano,largura,comprimento,deposito,consumomedio 
            FROM veiculos
            WHERE (veiculos.idveiculo=idveiculo)
            */

            stmtSql = string.Format("SELECT {0},{1},{2},{3},{4},{5},{6},{7} FROM {8} WHERE {9} = @IDVeiculo AND valido IS NULL", parameters);
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@IDVeiculo", SqlDbType.Int);
            sqlCmd.Parameters["@IDVeiculo"].Value = idVeiculo;

            rs = sqlCmd.ExecuteReader();

            veiculo = MapVeiculo(rs);

            rs.Close();

            return veiculo;
        }

        public void AdicionarVeiculo(Veiculo v)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "INSERT INTO veiculos (matricula,idtipo,ano,largura,comprimento,deposito,consumomedio) VALUES(@Matricula,@IDTipo,@Ano,@Largura,@Comprimento,@Deposito,@ConsumoMedio)";

            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@Matricula", SqlDbType.NChar);
            sqlCmd.Parameters["@Matricula"].Value = v.Matricula;
            sqlCmd.Parameters.Add("@IDTipo", SqlDbType.Int);
            sqlCmd.Parameters["@IDTipo"].Value = v.Tipo;
            sqlCmd.Parameters.Add("@Ano", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Ano"].Value = v.Ano;
            sqlCmd.Parameters.Add("@Largura", SqlDbType.Float);
            sqlCmd.Parameters["@Largura"].Value = v.Largura;
            sqlCmd.Parameters.Add("@Comprimento", SqlDbType.Float);
            sqlCmd.Parameters["@Comprimento"].Value = v.Comprimento;
            sqlCmd.Parameters.Add("@Deposito", SqlDbType.Int);
            sqlCmd.Parameters["@Deposito"].Value = v.Deposito;
            sqlCmd.Parameters.Add("@ConsumoMedio", SqlDbType.Float);
            sqlCmd.Parameters["@ConsumoMedio"].Value = v.Consumomedio;

            sqlCmd.ExecuteNonQuery();
        }

        public void RemoverVeiculo(int idveic)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "UPDATE veiculos SET valido = @Data WHERE idveiculo = @IDVeiculo";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@Data", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Data"].Value = DateTime.Now;
            sqlCmd.Parameters.Add("@IDVeiculo", SqlDbType.Int);
            sqlCmd.Parameters["@IDVeiculo"].Value = idveic;

            sqlCmd.ExecuteNonQuery();
        }


        public void AlterarVeiculo(Veiculo v)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;

            sqlCon = (SqlConnection)sqlConnection.Connection;
            stmtSql = "UPDATE veiculos SET matricula=@Matricula,idtipo=@IDTipo,ano=@Ano,largura=@Largura,comprimento=@Comprimento,deposito=@Deposito,consumomedio=@ConsumoMedio WHERE idveiculo=@IDveiculo";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@Matricula", SqlDbType.NChar);
            sqlCmd.Parameters["@Matricula"].Value = v.Matricula;
            sqlCmd.Parameters.Add("@IDTipo", SqlDbType.Int);
            sqlCmd.Parameters["@IDTipo"].Value = v.Tipo;
            sqlCmd.Parameters.Add("@Ano", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Ano"].Value = v.Ano;
            sqlCmd.Parameters.Add("@Largura", SqlDbType.Float);
            sqlCmd.Parameters["@Largura"].Value = v.Largura;
            sqlCmd.Parameters.Add("@Comprimento", SqlDbType.Float);
            sqlCmd.Parameters["@Comprimento"].Value = v.Comprimento;
            sqlCmd.Parameters.Add("@Deposito", SqlDbType.Int);
            sqlCmd.Parameters["@Deposito"].Value = v.Deposito;
            sqlCmd.Parameters.Add("@ConsumoMedio", SqlDbType.Float);
            sqlCmd.Parameters["@ConsumoMedio"].Value = v.Consumomedio;
            sqlCmd.Parameters.Add("@IDveiculo", SqlDbType.Int);
            sqlCmd.Parameters["@IDveiculo"].Value = v.IDveiculo;

            sqlCmd.ExecuteNonQuery();
        }

        private Veiculo MapVeiculo(DbDataReader rs)
        {
            Veiculo v = null;
            SqlDataReader sdr = (SqlDataReader)rs;

            if (sdr.Read())
            {
                v = new Veiculo();
                v.IDveiculo = (int)sdr[chavesVeiculos[0]];
                v.Matricula = ((String)sdr[camposVeiculos[0]]).Trim();
                v.Tipo = (int)sdr[camposVeiculos[1]];
                v.Ano = (DateTime)sdr[camposVeiculos[2]];
                v.Largura = (Double)sdr[camposVeiculos[3]];
                v.Comprimento = (Double)sdr[camposVeiculos[4]];
                v.Deposito = (int)sdr[camposVeiculos[5]];
                v.Consumomedio = (Double)sdr[camposVeiculos[6]];
            }
            sdr.Close();
            return v;
        }

        // OP_GET_VEICULOS_ORD

        public void MapVeiculosOrd(DbDataReader rs ,SortedSet<Veiculo> ss)
        {
            SqlDataReader sdr = (SqlDataReader)rs;
            Veiculo v;

            while (sdr.Read())
            {
                v = new Veiculo();
                v.IDveiculo = (int)sdr["idveiculo"];
                v.Matricula = ((String)sdr["matricula"]).Trim();
                v.Tipo = (int)sdr["idtipo"];
                v.Ano = (DateTime)sdr["ano"];
                v.Largura = (Double)sdr["largura"];
                v.Comprimento = (Double)sdr["comprimento"];
                v.Deposito = (int)sdr["deposito"];
                v.Consumomedio = (Double)sdr["consumomedio"];
                ss.Add(v);
            }
            sdr.Close();
        }

        public SortedSet<Veiculo> GetVeiculosOrd()
        {
            SortedSet<Veiculo> ss = new SortedSet<Veiculo>(new Veiculo.OrdenarAnoConsumo());
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            SqlDataReader rs;

            sqlCon = (SqlConnection)sqlConnection.Connection;
            stmtSql = "SELECT idveiculo,matricula,idtipo,ano,largura,comprimento,deposito,consumomedio FROM veiculos WHERE valido IS NULL ORDER BY ano DESC,consumomedio ASC";

            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            rs = sqlCmd.ExecuteReader();

            MapVeiculosOrd(rs,ss);

            return ss;
        }

        public SortedDictionary<Int32, String> GetListVeiculos()
        {
            SortedDictionary<Int32, String> sd = new SortedDictionary<Int32, String>();
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            SqlDataReader rs;
            Int32 idv;
            String matricula;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = string.Format("SELECT idveiculo,matricula FROM veiculos WHERE valido IS NULL");
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            rs = sqlCmd.ExecuteReader();

            while (rs.Read())
            {
                idv = (Int32)rs["idveiculo"];
                matricula = (String)rs["matricula"];
                sd.Add(idv, matricula);
            }

            rs.Close();

            return sd;
        }
    }
}
